package db.tables;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;

import control.ConnectionManage;
import db.parking.beans.Subscriber;


/**
 * AdminSubscriber manages all requests From,To table Subscriber
 * @author Operations
 *
 */
public class AdminSubscriber {
	
	private static Connection conn = ConnectionManage.getInstance().getConnection();


	
	
	/**
	 * @param int subscriber id
	 * @return Subscriber object from data base by id
	 * @throws SQLException
	 */
	public static Subscriber getRow(int sid) throws SQLException {

		String sql = "SELECT * FROM Subscriber  WHERE subID = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, sid);


			rs = stmt.executeQuery();

			if (rs.next()) {
				Subscriber bean = new Subscriber();
				bean.setSubID(rs.getInt("subID"));
				bean.setCusID(rs.getString("cusID"));
				bean.setCarNo(rs.getString("carNo"));
				bean.setSubStart(rs.getTimestamp("subStart"));
				bean.setType(rs.getString("type"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setLeaveTime(rs.getTimestamp("leaveTime"));
				bean.setLastLogin(rs.getTimestamp("lastLogin"));
				bean.setStatus(rs.getInt("status"));

				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	/**
	 * Functions returns only the active subscribers
	 * @param currTime
	 * @return
	 * @throws SQLException
	 */
	public static Subscriber[] checkSubscriptions(Timestamp currTime) throws SQLException {
		String sql = "SELECT * FROM subscriber WHERE ? > subStart";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, currTime);		

			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Subscriber bean[] = new Subscriber[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Subscriber();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setSubID(rs.getInt("subID"));
				bean[rowCount].setCusID(rs.getString("cusID"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setSubStart(rs.getTimestamp("subStart"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}

			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	/**
	 * Functions check what subscribers need to be sent a Renewal message
	 * @param currTime
	 * @return
	 * @throws SQLException
	 */
	public static Subscriber[] checkreNew(Timestamp currTime) throws SQLException {
		String sql = "SELECT * FROM subscriber WHERE ? > subStart AND status = 0";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, currTime);		

			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Subscriber bean[] = new Subscriber[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Subscriber();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setSubID(rs.getInt("subID"));
				bean[rowCount].setCusID(rs.getString("cusID"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setSubStart(rs.getTimestamp("subStart"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}

			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	
	/**
	 * @param subID
	 * @param carNo
	 * @return
	 * @throws SQLException
	 */
	public static Subscriber getRow(int subID,String carNo) throws SQLException {

		String sql = "SELECT * FROM Subscriber  WHERE subID = ? AND carNo = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, subID);
			stmt.setString(2, carNo);

			rs = stmt.executeQuery();

			if (rs.next()) {
				Subscriber bean = new Subscriber();
				bean.setSubID(rs.getInt("subID"));
				bean.setCusID(rs.getString("cusID"));
				bean.setCarNo(rs.getString("carNo"));
				bean.setSubStart(rs.getTimestamp("subStart"));
				bean.setType(rs.getString("type"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setLeaveTime(rs.getTimestamp("leaveTime"));
				bean.setLastLogin(rs.getTimestamp("lastLogin"));
				bean.setStatus(rs.getInt("status"));

			
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	/**
	 * @param cusID
	 * @param carNo
	 * @return
	 * @throws SQLException
	 */
	public static Subscriber getRow(String cusID,String carNo) throws SQLException {

		String sql = "SELECT * FROM Subscriber  WHERE cusID = ? AND carNo = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setString(1, cusID);
			stmt.setString(2, carNo);

			rs = stmt.executeQuery();

			if (rs.next()) {
				Subscriber bean = new Subscriber();
				bean.setSubID(rs.getInt("subID"));
				bean.setCusID(rs.getString("cusID"));
				bean.setCarNo(rs.getString("carNo"));
				bean.setSubStart(rs.getTimestamp("subStart"));
				bean.setType(rs.getString("type"));
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setLeaveTime(rs.getTimestamp("leaveTime"));
				bean.setLastLogin(rs.getTimestamp("lastLogin"));
				bean.setStatus(rs.getInt("status"));

			
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	/**
	 * Insert Full Subscriber entity into DB row Subscriber
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean insertFull(Subscriber bean) throws Exception {
    //complaintID is autoIncremented on Insert
		String sql = "INSERT into Subscriber (cusID , carNo , subStart , type , status) " +
				"VALUES (?, ?, ?, ?, ?)";
		ResultSet keys = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				) {
			
			stmt.setString(1, bean.getCusID());
			stmt.setString(2, bean.getCarNo());
			stmt.setTimestamp(3,bean.getSubStart());
			stmt.setString(4, bean.getType());
			stmt.setInt(5, bean.getStatus());
			
			
			int affected = stmt.executeUpdate();
			if(affected ==1){
				keys = stmt.getGeneratedKeys();
				keys.next();
				int newKey = keys.getInt(1);
	            bean.setSubID(newKey);
			}else {
				System.err.println("No Rows affected");
				return false;
			}
	            
		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally{
				 if (keys != null) 
			            keys.close();
		}
		return true;
		
	}

	/**
	 * Insert Full Subscriber entity into DB row Subscriber
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean insertRegular(Subscriber bean) throws Exception {
	    //complaintID is autoIncremented on Insert
			String sql = "INSERT into Subscriber (cusID , carNo , subStart , type , lotNo , leaveTime , status) " +
					"VALUES (?, ?, ?, ?, ? ,? , ?)";
			ResultSet keys = null;
			try (
					PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
					) {
				
				stmt.setString(1, bean.getCusID());
				stmt.setString(2, bean.getCarNo());
				stmt.setTimestamp(3,bean.getSubStart());
				stmt.setString(4, bean.getType());
				stmt.setInt(5, bean.getLotNo());
				stmt.setTimestamp(6,bean.getLeaveTime());
				stmt.setInt(7, bean.getStatus());


				int affected = stmt.executeUpdate();
				if(affected ==1){
					keys = stmt.getGeneratedKeys();
					keys.next();
					int newKey = keys.getInt(1);
		            bean.setSubID(newKey);
				}else {
					System.err.println("No Rows affected");
					return false;
				}
			
			} catch (SQLException e) {
				System.err.println(e);
				return false;
			} finally{
				if(keys != null)
					keys.close();
			}
			return true;
			
		}
	
	/**
	 * Update Subscription status
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean update(Subscriber bean) throws Exception {

		String sql =
				"UPDATE Subscriber SET " +
				"status = ? " +
				"WHERE subID = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,bean.getStatus());
			stmt.setInt(2,bean.getSubID());
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
	/**
	 * Functions updates user last login to the system
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean updateLastLogin(Subscriber bean) throws Exception {

		String sql =
				"UPDATE Subscriber SET " +
				"lastLogin = ? " +
				"WHERE subID = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setTimestamp(1,bean.getLastLogin());
			stmt.setInt(2,bean.getSubID());
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	
	/**
	 * Functions returns number of cars in lot that is owned by a specified id
	 * @param cusId
	 * @param LotNo
	 * @return
	 * @throws Exception
	 */
	public static int getNumOfCarsInLot(String cusId,int LotNo) throws Exception {
		ResultSet rs = null;

		String sql = "SELECT * FROM Subscriber WHERE cusID =?  AND lotNo = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setString(1,cusId);
			stmt.setInt(2,LotNo);
			rs = stmt.executeQuery();

			rs.last();
			int totalRows = rs.getRow();
			
			return totalRows;
		}
		catch(SQLException e) {
			System.err.println(e);
			return 0;
		}

	}
	
	//subscriber
	/**
	 * Functions gets subscriber ID number from certain lot by time interval
	 * @param startDay
	 * @param endDay
	 * @param lotNo
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings({ "deprecation" })
	public static int getSubscribersNumInLot(Timestamp startDay,Timestamp endDay, int lotNo) throws SQLException {


		String sql = "SELECT * FROM Subscriber  WHERE lotNo = ? AND subStart<=? AND subStart > ?";
		ResultSet rs = null;

		startDay.setHours(0);
		startDay.setMinutes(0);
		startDay.setSeconds(0);
		startDay.setNanos(0);
		
		endDay.setHours(0);
		endDay.setMinutes(0);
		endDay.setSeconds(0);
		endDay.setNanos(0);

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1, lotNo);
			stmt.setTimestamp(2, startDay);
			stmt.setTimestamp(3, endDay);
			rs = stmt.executeQuery();
			rs.last();
			int numOfOrders = rs.getRow();
			return numOfOrders;		
		} catch (SQLException e) {
			System.err.println(e);
			return -1;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	/**
	 * return number of temporary orders that are actually done	
	 * @param startDay
	 * @param endDay
	 * @param lotNo
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("deprecation")
	public static int getSubscribersNumInLotMoreThanOneCar(Timestamp startDay,Timestamp endDay, int lotNo) throws Exception {
		String sql = "SELECT * " +
				"FROM  subscriber s INNER JOIN  customer c ON c.cusID = s.cusID " +
				"WHERE  s.subStart >= ? AND s.subStart < ?  AND s.lotNo=? AND c.numOfCars > 1";
		
	
		startDay.setHours(0);
		startDay.setMinutes(0);
		startDay.setSeconds(0);
		startDay.setNanos(0);
		
		endDay.setHours(0);
		endDay.setMinutes(0);
		endDay.setSeconds(0);
		endDay.setNanos(0);
	
		
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, startDay);
			stmt.setTimestamp(2, endDay);
			stmt.setInt(3, lotNo);
			rs = stmt.executeQuery();
			rs.last();
			int numOfSubscribers = rs.getRow();
			return numOfSubscribers;
		} catch (SQLException e) {
			System.err.println(e + "AdminSubscribers-> getSubscribersNumInLotMoreThanOneCar");
			return -1;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}

	/**
	 * Get number of subscribers from all lots
	 * @param startDay
	 * @param endDay
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("deprecation")
	public static int getSubscribersNumInAllLots(Timestamp startDay,Timestamp endDay) throws SQLException {

		String sql = "SELECT * FROM Subscriber  WHERE subStart<= ? AND subStart >= ?";
		ResultSet rs = null;

		startDay.setHours(0);
		startDay.setMinutes(0);
		startDay.setSeconds(0);
		startDay.setNanos(0);
		
		endDay.setHours(0);
		endDay.setMinutes(0);
		endDay.setSeconds(0);
		endDay.setNanos(0);
		

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setTimestamp(1, startDay);
			stmt.setTimestamp(2, endDay);
			rs = stmt.executeQuery();
			rs.last();
			int numOfOrders = rs.getRow();
			return numOfOrders;		
		} catch (SQLException e) {
			System.err.println(e);
			return -1;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	/**
	 * return num of temporary orders the actually done	
	 * @param startDay
	 * @param endDay
	 * @return
	 * @throws SQLException
	 */
	@SuppressWarnings("deprecation")
	public static int getSubscribersNumInAllLotsMoreThanOneCar(Timestamp startDay,Timestamp endDay) throws SQLException  {
		
		String sql = "SELECT c.cusID " +
				"FROM customer c JOIN subscriber s ON c.cusID = s.cusID " +
				"WHERE  s.subStart<= ? AND s.subStart >= ?  AND c.numOfCars > '1'";
		
		// calc the start of the day like 2014-02-26 00:00:00 000	

		startDay.setHours(0);
		startDay.setMinutes(0);
		startDay.setSeconds(0);
		startDay.setNanos(0);
		
		endDay.setHours(0);
		endDay.setMinutes(0);
		endDay.setSeconds(0);
		endDay.setNanos(0);
	
		
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setTimestamp(1, startDay);
			stmt.setTimestamp(2, endDay);
			rs = stmt.executeQuery();
			rs.last();
			int numOfSubscribers = rs.getRow();
			return numOfSubscribers;
		} catch (SQLException e) {
			System.err.println(e + "AdminSubscribers-> getSubscribersNumInLotMoreThanOneCar");
			return -1;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
	
	
	
	public static boolean delete(int oId) throws Exception {

		String sql = "DELETE FROM subscriber WHERE subID = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,oId);
			int affected = stmt.executeUpdate();
			if(affected == 1){
					return true;
			} else {
				return false;
			}
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}
	}

	public static Subscriber[] getAllSubscribers() throws SQLException {
		String sql = "SELECT * FROM subscriber ORDER BY lotNo";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
		
			
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Subscriber bean[] = new Subscriber[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Subscriber();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setSubID(rs.getInt("subID"));
				bean[rowCount].setCusID(rs.getString("cusID"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setSubStart(rs.getTimestamp("subStart"));
				bean[rowCount].setType(rs.getString("type"));
				bean[rowCount].setLotNo(rs.getInt("lotNo"));
				bean[rowCount].setLeaveTime(rs.getTimestamp("leaveTime"));
				bean[rowCount].setLastLogin(rs.getTimestamp("lastLogin"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			}
			return bean;
		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
	
}
